package com.itheima01.dao;

import com.itheima01.domian.Student;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class StudentDao implements StudentDaoIf{
    private ArrayList<Student> stulist=new ArrayList<>();
    private Connection con=null;
    private ResultSet rs=null;
    private Statement statement=null;
    private void startR(){
        //建立连接和执行者对象
        try{
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db14","root","root");
            statement = con.createStatement();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    private void finalR(){//结束判断,释放资源
        if (con!=null){
            //这里判断不为null,其实是判断是否出现异常,导致变量没赋值
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement!=null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    @Override
    public List<Student> findAll() {
        try {
            startR();
            rs = statement.executeQuery("SELECT * FROM student");
            while (rs.next()){
                Integer sid = rs.getInt("sid");
                String name = rs.getString("name");
                Integer age = rs.getInt("age");
                Date birthday = rs.getDate("birthday");
                stulist.add(new Student(sid,name,age,birthday));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            finalR();
        }
        return stulist;
    }
    @Override
    public Student findId(Integer id) {
        Student student=null;
        try {
            startR();
            rs = statement.executeQuery("SELECT * FROM student WHERE sid='"+id+"'");
            //先往下移动一行再判断是否有数据
                while (rs.next()){
                    Integer sid = rs.getInt("sid");
                    String name = rs.getString("name");
                    Integer age = rs.getInt("age");
                    Date birthday = rs.getDate("birthday");
                    student = new Student(sid, name, age, birthday);
                }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            finalR();
        }
        return student;
    }

   @Override
    public int addStudent(Student student) {
        int result = 0;
       try {
           startR();
           SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
           String date = simpleDateFormat.format(student.getBirthday());
           String sql="INSERT INTO student VALUES('"+student.getSid()+"','"+student.getName()+"','"+student.getAge()+"','"+date+"')";
           System.out.println(sql);
           result = statement.executeUpdate(sql);
       } catch(SQLException e){
           e.printStackTrace();
       }finally {
           finalR();
       }
       return result;
    }
}
